package com.accenture.job.executor.mapper;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;
import com.accenture.job.executor.model.RiskLevel;

/**
 * 分析组RiskModel Mapper
 * 
 * @author binbin.a.zhang
 */
@Mapper
public interface RiskModelMapper {
	/**
	 * 新增 待审核费用明细数据到ShareDB
	 * 
	 * @return insertRowCount
	 * @author binbin.a.zhang
	 */
	@Insert("   INSERT INTO [SHARE_DB].[DBO].[TE_ANALYSIS_ORIGINALEXPENSE] "
			+ " SELECT '' BATCHNO,'' PROCESSINGSTEP,O.LEGALENTITYCODE,O.ORGNAMEPATH,O.COMPANYCODE,C.COSTCENTERCODE,O.REFERENCENO, "
			+ " E.EMPLOYEECODE,E.EMPLOYEENAME,E.MAIL,OE.NO,ET.NAME,O.TOTALREIMBURSEMENTAMOUNT, "
			+ " '' RECEIVINGTIME,'' PROCESSINGTIME,CAST( CAST(O.CREATEDATE AS NVARCHAR) AS DATETIME2),CAST(CAST( O.UPDATEDATE AS NVARCHAR) AS DATETIME2) "
			+ " FROM [TE_APP_DEV].[DBO].TE_WA_ORIGINALREIMBURSEMENT O "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_COSTCENTER C ON O.COSTCENTERID=C.COSTCENTERID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_CUSTOMEREMPLOYEE E ON O.ApplicantId =E.EMPLOYEEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_WA_ORIGINALEXPENSEELEMENT OE ON O.REIMBURSEMENTID=OE.REIMBURSEMENTID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_EXPENSETYPE ET ON OE.EXPENSETYPEID=ET.EXPENSETYPEID")
	int inrestOriginalExpense();

	/**
	 * 新增 NC表数据到ShareDB
	 * 
	 * @return insertRowCount
	 * @author binbin.a.zhang
	 */
	@Insert(" INSERT INTO [SHARE_DB].[DBO].[TE_ANALYSIS_NONCOMPLIANCE] "
			+ " SELECT  T05.BATCHNO AS BATCHNO ,T04.PROCESSINGSTEP AS PROCESSINGSTEP ,T03.LEGALENTITYCODE AS LEGALENTITYCODE "
			+ " ,T03.ORGNAMEPATH AS ORGNAMEPATH ,T03.COMPANYCODE AS COMPANYCODE ,T06.COSTCENTERCODE AS COSTCENTERCODE "
			+ " ,T03.REFERENCENO AS REFERENCENO ,T07.EMPLOYEECODE AS EMPLOYEECODE,T07.EMPLOYEENAME AS EMPLOYEENAME,T07.MAIL AS EMPLOYEEMAIL"
			+ " ,T02.NO AS EXPENSENO,T08.NAME AS EXPENSETYPE,T02.APPLYEXPENSEAMOUNT AS APPLYEXPENSEAMOUNT ,T01.CHECKPOINTCATEGORYNAME AS CHECKPOINTCATEGORYNAME "
			+ " ,T01.CHECKPOINTDESCRIPTION AS CHECKPOINTDESCRIPTION,T11.NCTYPENAME AS NCTYPENAME,T01.REMARK AS REMARK,'' AS RECEIVINGTIME,'' AS PROCESSINGTIME "
			+ " ,CAST( CAST(T01.CREATEDATE AS NVARCHAR) AS DATETIME2)  AS CREATEDATE,CAST(CAST( T01.UPDATEDATE AS NVARCHAR) AS DATETIME2) AS UPDATEDATE"
			+ " FROM ( SELECT REIMBURSEMENTID ,0 AS EXPENSEID ,NCTYPEID,ISCLOSE,CLOSETIME,COMMENT,CREATEDATE,CREATEUSERID,UPDATEUSERID "
			+ " ,CHECKPOINTCATEGORYNAME,CHECKPOINTDESCRIPTION,CHECKPOINTCONTENTDESCRIPTION,CHECKPOINTVERSION,REMARK,UPDATEDATE "
			+ " FROM [TE_APP_DEV].[DBO].TE_AR_REIMBURSEMENTNC (NOLOCK)" + " UNION ALL "
			+ " SELECT REIMBURSEMENTID ,EXPENSEID ,NCTYPEID,ISCLOSE,CLOSETIME ,COMMENT ,CREATEDATE,CREATEUSERID ,UPDATEUSERID,"
			+ " CHECKPOINTCATEGORYNAME,CHECKPOINTDESCRIPTION ,CHECKPOINTCONTENTDESCRIPTION ,CHECKPOINTVERSION,REMARK,UPDATEDATE "
			+ " FROM [TE_APP_DEV].[DBO].TE_AR_EXPENSEELEMENTNC (NOLOCK) ) T01"
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_AR_EXPENSEELEMENT (NOLOCK)  T02 ON T01.EXPENSEID = T02.EXPENSEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_AR_REIMBURSEMENT (NOLOCK)  T03 ON T01.REIMBURSEMENTID = T03.REIMBURSEMENTID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_AR_TRANSACTION (NOLOCK)  T04 ON T03.REIMBURSEMENTID = T04.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_BATCH (NOLOCK)  T05 ON T04.BATCHID = T05.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_COSTCENTER T06 ON T03.COSTCENTERID = T06.COSTCENTERID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_CUSTOMEREMPLOYEE T07 ON T03.APPLICANTID = T07.EMPLOYEEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_EXPENSETYPE T08 ON T02.EXPENSETYPEID = T08.EXPENSETYPEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_EXPRESS (NOLOCK)  T09 ON T04.POSTEXPRESSCODE = T09.EXPRESSNO "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_GE_SHIPPER T10 ON T09.SHIPPERID = T10.SHIPPERID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_NCTYPE T11 ON T01.NCTYPEID = T11.NCTYPEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_USER T12 ON T01.CREATEUSERID = T12.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_USER T13 ON T01.UPDATEUSERID = T13.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_NONCOMPLIANCEFOLLOWUP (NOLOCK)  T14 ON T01.REIMBURSEMENTID = T14.REIMBURSEMENTID "
			+ " AND T14.STATUS=0 LEFT JOIN TE_MD_USER T15 ON T14.FOLLOWERID = T15.ID WHERE 1=1 ")
	int inrestNoncompliance();

	/**
	 * 新增 审核完成费用明细表（TE_Analysis_CompleteExpense）
	 * 
	 * @return insertRowCount
	 * @author binbin.a.zhang
	 */
	@Insert(" INSERT INTO [SHARE_DB].[DBO].[TE_ANALYSIS_COMPLETEEXPENSE] "
			+ " SELECT T05.BATCHNO AS BATCHNO ,T04.PROCESSINGSTEP AS PROCESSINGSTEP,T01.LEGALENTITYCODE AS LEGALENTITYCODE "
			+ " ,T01.ORGNAMEPATH AS ORGNAMEPATH,T01.COMPANYCODE AS COMPANYCODE,T06.COSTCENTERCODE AS COSTCENTERCODE,T01.REFERENCENO AS REFERENCENO "
			+ " ,T07.EMPLOYEECODE AS EMPLOYEECODE,T07.EMPLOYEENAME AS EMPLOYEENAME,T07.MAIL AS EMPLOYEEMAIL,T02.NO AS EXPENSENO "
			+ " ,T08.NAME AS EXPENSETYPE,T02.APPLYEXPENSEAMOUNT AS APPLYEXPENSEAMOUNT ,'' AS RECEIVINGTIME "
			+ " ,'' AS PROCESSINGTIME,CAST( CAST(T01.CREATEDATE AS NVARCHAR) AS DATETIME2)  AS CREATEDATE "
			+ " ,CAST(CAST( T01.UPDATEDATE AS NVARCHAR) AS DATETIME2) AS UPDATEDATE FROM [TE_APP_DEV].[DBO].TE_AR_REIMBURSEMENT T01 "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_AR_EXPENSEELEMENT (NOLOCK)  T02 ON T01.REIMBURSEMENTID = T02.REIMBURSEMENTID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_AR_TRANSACTION (NOLOCK)  T04 ON T01.REIMBURSEMENTID = T04.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_BATCH (NOLOCK)  T05 ON T04.BATCHID = T05.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_COSTCENTER T06 ON T01.COSTCENTERID = T06.COSTCENTERID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_CUSTOMEREMPLOYEE T07 ON T01.APPLICANTID = T07.EMPLOYEEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_EXPENSETYPE T08 ON T02.EXPENSETYPEID = T08.EXPENSETYPEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_EXPRESS (NOLOCK)  T09 ON T04.POSTEXPRESSCODE = T09.EXPRESSNO "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_GE_SHIPPER T10 ON T09.SHIPPERID = T10.SHIPPERID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_REFERENCETYPE T11 ON T01.REFERENCETYPEID = T11.REFERENCETYPEID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_USER T12 ON T01.CREATEUSERID = T12.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_USER T13 ON T01.UPDATEUSERID = T13.ID "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_RU_NONCOMPLIANCEFOLLOWUP (NOLOCK)  T14 ON T01.REIMBURSEMENTID = T14.REIMBURSEMENTID AND T14.STATUS=0 "
			+ " LEFT JOIN [TE_APP_DEV].[DBO].TE_MD_USER T15 ON T14.FOLLOWERID = T15.ID  ")
	int inrestCompleteExpense();

	/**
	 * 同步 分析组RiskModel数据到ITE
	 * 
	 * @return synchronousRiskLevel
	 * @author binbin.a.zhang
	 */
	@SuppressWarnings("rawtypes")
	@Select("EXEC  [TE_APP_DEV].[DBO].SYNCHRONOUSRISKLEVEL")
	@Options(statementType = StatementType.CALLABLE)
	HashMap synchronousRiskLevel();
	
	/**
	 * 删除 分析组RiskModel
	 * 
	 * @return void
	 * @author binbin.a.zhang
	 */
	@Delete ("truncate table  [Share_DB].[dbo].[TE_Analysis_RiskLevel] ")
	void deleteRiskLevel();
	
	
	
	

	/**
	 * 获取分析组RiskModel计算结果数据
	 * 
	 * @return getRsikLevelList
	 * @author binbin.a.zhang
	 */
	@Select("  SELECT REFERENCENO,REFSCORE,ITEMSCORE, EXPENSENO, "
			+ " CASE REFLEVEL WHEN N'低' THEN 0  WHEN N'中' THEN 1  WHEN N'高' THEN 2  END REFLEVEL ,"
			+ " CASE ITEMLEVEL WHEN N'低' THEN 0  WHEN N'中' THEN 1  WHEN N'高' THEN 2  END ITEMLEVEL  "
			+ " FROM [SHARE_DB].[DBO].[TE_ANALYSIS_RISKLEVEL]")
	List<RiskLevel> getRsikLevelList();

	/**
	 * 批量更新RiskModel计算结果到TE_WA_OriginalExpenseElement
	 * ps：<script>是动态sql标签，<foreach>是循环标签 separator是按照;拼接
	 * @param list
	 * @return
	 */
	@Update("<SCRIPT> <FOREACH ITEM='VALUE' INDEX='KEY' COLLECTION='LIST' SEPARATOR=';'>"
			+ "UPDATE TE_APP_DEV.DBO.TE_WA_ORIGINALREIMBURSEMENT   SET POINTFORANALYTICAL=#{VALUE.REFSCORE} ,"
			+ "CLASSIFICATIONFORANALYTICAL=#{VALUE.REFLEVEL} WHERE REFERENCENO=#{VALUE.REFERENCENO}"
			+ "</FOREACH></SCRIPT>")
	int updateOriginalReimbursement(@Param("list") List<RiskLevel> list);
}
